In spite of the threat that is presented to data security by SQL Injection, many programmers and DBAs are either unaware of it, or do not know how to properly prevent it. This is partly because SQL injection, and methods to prevent it, are so rarely talked about in formal education: I went through two classes on database theory, several books on SQL Server, and a MCDBA before I first really learned about SQL Injection through “The curse and blessings of dynamic SQL” by Erland Sommarskog.

Because of this, SQL injection remains a common and effective attack. In a significant recent case, even a firm dedicated to security was at least partially compromised through a SQL injection attack (See ‘Anonymous speaks: the inside story of the HBGary hack’), providing salutary lessons to the industry on what can go wrong.

There are already a plethora of articles about protecting against SQL Server injection on SQL Server. Yet, few of them emphasise that the best defense against such attacks is a defense in depth, with a whole range of precautions. Many of these articles focus almost entirely on parameterizing SQL as the defense against SQL Injection. While parameterizing is the first and best defense against SQL Injection, it should not be the only one. Thus, I decided to add one more to list examining various layers of defense and using python for the examples.

What is SQL Injection?

SQL Injection attacks are carried out by passing specially-formatted strings as input. In a successful attack, those special strings are passed along to a database to either execute arbitrary code or cause the server to return unanticipated results. For example, if we have a python program using pyodbc which concatenates user input into a SQL query like this:

Then a malicious user who carefully formats the zipcode entry could execute unintended SQL commands. For instance, if the user provided:

Then the profiler would show that the server would receive:

Assuming the program had the proper permissions, the server would obediently drop the test table.

Basic Techniques to Prevent SQL Injection

Parameterize all Queries

The first, best, line of defense against SQL Injection is to parameterize all SQL queries in code. If the previous example using pyodbc had been parameterized, it could look like:

This causes the profiler to receive quite a few messages, but the key part is:

Since it received the malicious code as a variable, the server would simply look for the value in the table and return a blank result sets. The malicious string is never executed, so the test table is never dropped.

Similarly, most ORMs like SQLAlchemy will automatically parameterize all SQL statements under normal circumstances. Thus, they provide a good initial defense against SQL injection.

Use Only Stored Procedures

The use of stored procedures by themselves does not provide direct protection against SQL injection, although it can properly be used as part of a more comprehensive defense. To see why stored procedures cannot by themselves protect against SQL injection, consider one that queries to retrieve the city and state for a zip code like:

Then if there is a python program that executes:

Then an attacker might provide:

As the input, which will send…

…to the server. Which, again assuming proper permissions, would drop the Test table just as it did without the stored procedure.

Of course, that type of attack can be prevented by parameterizing the input, just as with the standard select statement. However, if the stored procedure itself uses dynamic SQL that is made through concatentation, then the stored procedure may execute the malicious commands even if the calling program properly parameterizes. This can be prevented by parameterizing dynamic SQL in stored procedures through sp_executesql and is discussed in “The Curse and Blessings of Dynamic SQL“.

The greatest value for using stored procedures in preventing SQL injection is that the DBA can set permissions for the application account so that its only way to interact with the SQL Server is through stored procedures. (See SQL Server Security Workbench Part 1 ) This would mean that most SQL injection attacks would fail due to lack of permissions even if the calling program did not parameterize. This of course still leaves open the possibility of SQL injection working through dynamic SQL inside the stored procedures, but the stored procedures can be given an “execute as” clause which limits their permissions to only those needed by the procedure. It is generally easier to verify that all stored procedures are written to guard against SQL injection then it is to check every place where the application interacts with the SQL Server.

Limiting Permissions

This naturally leads to a very effective method of preventing some attacks and limiting the damages from SQL injection attacks, namely using the account with the lowest permissions possible for a job. If the account being used does not have permission to drop a table, then it will not be dropped even if the command is slipped to SQL Server. Similarly, if the account has only read access, an attacker might be able to gain some information, which can certainly cause problems, but the attacker will not be able to modify or destroy the data, which is frequently worse. Even read permissions can be strictly limited in SQL server, to limit which tables can be viewed. If the application only needs selected columns from a table, then read permission on the view can be granted rather than the full table.

Validating input

User input should always be treated with care and there are a number of reasons to validate all the user input before further processing. Validation code can also help to avoid wasting server resources by restricting requests that would not return useful results and they can provide much more helpful messages to the user than a SQL error message or empty result set would likely provide. They can also help stop SQL Injection by rejecting, outright, any forms of input that could be used to perform a SQL injection.

Because of its many advantages, it is always important to validate user input, but it is particularly significant when the user input is being passed on to other routines for further processing, or in some of the rare cases where it is impossible to fully parameterize the input. For instance, if you are dealing with the rare situation where the users are required to provide a table name for a DDL statement, the table name cannot be passed in as a parameter and must be concatenated at some point. In that situation, validation of the input is a crucial defense against injection attacks. Similarly, if the input is passed in to a stored procedure, then it is possible that the stored procedure will use it to generate dynamic SQL via concatenation, even if the program properly parameterizes the procedure call. With the benefits that validation can bring, it is generally wise to validate all user input, even when fully parameterizing database calls and using an account with limited permissions.

Concealing Error Messages

Injection attacks often depend on the attacker having at least some information about the database schema. He often gains this through trial and error, and the error messages will tell the attacker quite a lot about the schema. Both SQL Server and python generally provide clear, informative error messages that are incredibly helpful to programmers, but can also provide information to a malicious user. Pyodbc, in particular, will normally raise a pyodbc.ProgrammingError exception, which helpfully includes the SQL Server error message.

Encasing a python call to SQL Server in a try/except block will enable the program to provide a more user friendly error message, which does not contain useful information for attackers, to the end user. If used along with something like sys.exc_info and a logging package, the except blocks can log all errors for later analysis while displaying a user friendly message to the end user. A very basic example might look like:

Of course, to ensure no unfiltered messages get through it is possible to override the standard exception hook like:

This will not have any impact on exception handling code, but it will prevent standard error messages from reaching a user for unhandled exceptions.

Limiting Damage

As well as taking steps to prevent attacks like SQL injection, there are other general security steps that can be taken to limit the damage. Limiting the permissions of the accounts used has been mentioned, as it can stop many attacks outright, but it will also limit the amount of damage that can be done by a successful attack. But there are other methods that can help mitigate the damage done by an attack.

Use encryption/hash functions where appropriate

When data is properly encrypted, it can be made of little value to someone without an encryption key. Cell level encryption can assist with protecting against unauthorized access to sensitive data SQL Server has supported it since SQL Server 2005. Transparent Data Encryption (TDE), while useful for protecting the database against other forms of attack, is of very little value against SQL injection.

Passwords in particular should not be stored in clear text, and hashing is generally better than encrypting as it makes it harder to recover the original plaintext. Of course, even hashing may provide only limited security if it is not handled properly. For example, rainbow tables (http://en.wikipedia.org/wiki/Rainbow_table) exist for the MD5 algorithm. They make it relatively practical to determine the original plaintext from a single iteration of MD5 without salting. There are libraries that can make hashing and salting a password relatively easy in python, including hashlib.

Segregate data

Segregating data into different systems, depending on the level of security it needs, can help limit the reach of an attack. It often even makes sense to ensure that truly sensitive data is stored in a way that is not accessible from an outside network. This helps to ensure that even if an attacker compromises a system, that it will not immediately lead to the attacker compromising all systems. Of course, it is necessary to ensure that the same log on credentials are not shared between the segregated systems, otherwise if one set of log on credentials is compromised in some way it may lead directly to compromising other systems.

Auditing and Logging

Auditing and logging will never help prevent SQL injection or any other attack. However, it is likely to help detect the attacks, and may help in recovering from it. There are a number of tools within SQL Server such as Change Data Capture and SQL Server Audit. Custom written triggers could also be used to monitor and log changes. There are also a number of external tools that can provide more options such as SQL Server Compare and SQL Server Data Compare. The Logging library and other similar libraries make logging from the python application also relatively easy.

Conclusion

SQL injection is one of the more common, and more effective, forms of attack on a system. By following principles of secure software design such as parameterizing input to the database, sanitizing and validating user input, and restricting the permissions given to all accounts to the minimum required it is possible to make it extremely difficult for a SQL injection attack to succeed. Also, by following basic security practices like encrypting sensitive data, segregating data, and maintaining logs it is possible to limit the amount of damage that even a successful attack can do.

More references

• SQL Server Security Workbench Part 1  by Robyn Page and Phil Factor
• Database Encryption in SQL Server 2008 Enterprise Edition by Sung Hsueh
• SQL Injection! by Christoffer Hedgate
• SQL Injection – Part 1 by Randy Dyess
• Updated SQL Injection by Michael Coles
• Do Stored Procedures Protect Against SQL Injection by Brian Swan
• XKCD: Exploits of a Mom by Randall Munroe